﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DiBang.Common;

namespace DiBang.Sql
{

    /// <summary>
    /// 一些平常使用的Sql方法
    /// </summary>
    public class SqlExtend
    {
        SqlLib sqlLib = new SqlLib();

        /// <summary>
        /// 根据修改的数据生成History
        /// </summary>
        /// <param name="lasteventtimekey">LAST_EVENT_TIMEKEY</param>
        public void MakeDiBangHistory(string lasteventtimekey)
        {
            string historyTable = "DB_DIBANG_HISTORY";
            string mainTable = "DB_DIBANG";

            Dictionary<string, string> map = new Dictionary<string, string>();//设置History和Main的对应列
            map.Add("DIBANG_ID", "DIBANG_ID");
            map.Add("CHENG_DATE", "CHENG_DATE");
            map.Add("PIN_MING", "PIN_MING");
            map.Add("CHE_HAO", "CHE_HAO");
            map.Add("WEIGHT", "WEIGHT");
            map.Add("MaoZhong", "MaoZhong");
            map.Add("PiZhong", "PiZhong");
            map.Add("RongQiNum", "RongQiNum");
            map.Add("RongQiZhong", "RongQiZhong");
            map.Add("JingZhong", "JingZhong");
            map.Add("OTHER", "OTHER");
            map.Add("IMAGE_ID", "IMAGE_ID");
            map.Add("CompleteFlag", "CompleteFlag");
            map.Add("CREATE_USER_ID", "CREATE_USER_ID");
            map.Add("CREATE_TIME", "CREATE_TIME");
            map.Add("LAST_EVENT_NAME", "LAST_EVENT_NAME");
            map.Add("LAST_EVENT_USER_ID", "LAST_EVENT_USER_ID");
            map.Add("LAST_EVENT_TIMEKEY", "LAST_EVENT_TIMEKEY");

            string ssql = "";
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            strSql1.Append("insert into ").Append(historyTable).Append("(");
            strSql2.Append(" select ");
            foreach (KeyValuePair<string, string> pair in map)
            {
                strSql1.Append(pair.Key).Append(",");
                strSql2.Append(pair.Value).Append(",");
            }
            string sql1 = strSql1.ToString();
            sql1 = sql1.Substring(0, sql1.Length - 1) + ")";//字符串组装结束后,结尾为',',需要把','去掉
            string sql2 = strSql2.ToString();
            sql2 = sql2.Substring(0, sql2.Length - 1) + " from " + mainTable + " where LAST_EVENT_TIMEKEY={0}LAST_EVENT_TIMEKEY";//字符串组装结束后,结尾为',',需要把','去掉
            ssql = sql1 + " " + sql2;

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format(ssql, ConstParam.DbChar);
            cmd.Parameters.Add(new SqlParameter("LAST_EVENT_TIMEKEY",lasteventtimekey));

            sqlLib.RunCommand(cmd);
        }

        /// <summary>
        /// 检查用户名和密码是否正确
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="userPwd"></param>
        /// <returns></returns>
        public bool UserPwdCheck(string userId,string userPwd)
        {
            if (string.IsNullOrEmpty(userId))
                throw new Exception("用户ID不能为空!");

            if (string.IsNullOrEmpty(userPwd))
                throw new Exception("用户密码不能为空!");

            string ssql = @"SELECT 1 FROM DB_USER where USER_ID={0}USER_ID and USER_PWD={0}USER_PWD and ACTIVE='Y'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format(ssql, ConstParam.DbChar);
            cmd.Parameters.Add(new SqlParameter("USER_ID", userId));
            cmd.Parameters.Add(new SqlParameter("USER_PWD",Base64Encoding.BaseEncoding(userPwd)));//密码进行编码后再传输

            DataTable dt = sqlLib.GetDataSet(cmd).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取DB_USER的内容
        /// </summary>
        /// <param name="userid">USER_ID</param>
        /// <returns>USER_ID/USER_NAME/USER_PWD/USER_GROUP</returns>
        public UserInfo GetUserInfo(string userid)
        {
            string ssql = @"SELECT USER_ID,
USER_NAME,
USER_PWD,
USER_GROUP FROM DB_USER where USER_ID={0}USER_ID";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format(ssql, ConstParam.DbChar);
            cmd.Parameters.Add(new SqlParameter("USER_ID", userid));

            DataTable dt = sqlLib.GetDataSet(cmd).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                UserInfo userinfo = new UserInfo();
                userinfo.UserID = dt.Rows[0]["USER_ID"].ToString();
                userinfo.UserName = dt.Rows[0]["USER_NAME"].ToString();
                userinfo.UserPwd = dt.Rows[0]["USER_PWD"].ToString();
                userinfo.UserGroup = dt.Rows[0]["USER_GROUP"].ToString();

                return userinfo;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据库TimeKey
        /// </summary>
        /// <returns>2006-05-16 10:57:49.700</returns>
        public string GetTimeKey()
        {
            //尽量使用DB的时间生成TimeKey,与记录信息的时间保持一致
            //string timekey = DateTime.Now.ToString("yyyyMMddHHmmssffff");
            string timekey = string.Empty;
            string ssql = "Select CONVERT(varchar(100), GETDATE(), 121) as TIMEKEY";//2006-05-16 10:57:49.700
            DataTable dt = sqlLib.GetDataSet(ssql).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                timekey = dt.Rows[0]["TIMEKEY"].ToString();
            }
            else
            {
                throw new Exception("获取数据库时间失败!");
            }
            return timekey;
        }

        /// <summary>
        /// 获取数据库时间
        /// </summary>
        /// <returns>2006-05-16 10:57:49</returns>
        public string GetSysTime()
        {
            string strTime = string.Empty;
            string ssql = "Select CONVERT(varchar(100), GETDATE(), 120) as SYS_TIME";//2006-05-16 10:57:49
            DataTable dt = sqlLib.GetDataSet(ssql).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                strTime = dt.Rows[0]["SYS_TIME"].ToString();
            }
            else
            {
                throw new Exception("获取数据库时间失败!");
            }
            return strTime;
        }
    }
}
